Server-side enforcement of complex, interrelated rules, part 3

By David Wendelken

David Wendelken is a senior consultant for Tactics, Inc. He provides coaching services for those who want to improve their Oracle software development practices and is the co-author of The Oracle Designer/2000 Handbook with Carrie Anderson. You can reach him at (404) 248-1226 or via E-mail at 73523.2344@compuserve.com.

Editor’s note: This is the third installment of our series on implementing complex server-side rules. See the October issue for goals 1 through 4 and the November issue for goals 5 through 8.

When I was a young lad in school, I noticed that various teachers took daily attendance differently. Some didn’t bother with attendance, while others took the roll and recorded the result. But, and this was the interesting thing, some made a mark in the attendance book when a student was present, others when the student was absent. Why? And did it make a difference?

It wasn’t until many years had passed that I understood the answer. (I was a young lad, after all!) Logically speaking, we have three different ways of recording attendance, which are shown in Table A.

Table A: These are three commonly used methods of taking attendance.

As for why someone would choose one method over another, the answer is based in mathematics. Imagine that you must record attendance for five classrooms of 30 students for 180 days a year. Using the positive attendance method, with (on average) 90 percent of the students present in each class, our teacher must make 5 x 30 x 180 x 0.90 marks in the attendance log per year. For the computationally challenged, that comes out to 24,300 little check marks. If the teacher had used the negative attendance method, the numbers would be 5 x 30 x 180 x 0.10 marks, or 2,700 check marks. Over a 30-year career, that comes to… well, you get the idea.

Of course, if only the pure efficiency factor in recording attendance were at work, we’d choose the "Don’t Bother" method. But, if we have to be able to produce highly accurate attendance records, then that method won’t work. Naturally, if a teacher worked in the sort of school where the average daily attendance percentage was reversed, with only 10 percent of the students showing up, the positive recording method would be more efficient.

What does classroom attendance have to do with server-side security enforcement? Well, some companies don’t want to bother with security. A mom-and-pop shop doesn’t need fancy, multitiered security setups. Other organizations, such as those who record highly confidential data (the CIA or credit bureaus), must be very careful about what data they allow users to access. They prefer to prevent all access to the data unless the user is specifically authorized. This corresponds to the positive attendance method, because gaining access requires that a mark be made in some security file. A company in the middle of the spectrum believes that most of its information should be open to its employees, but some of it (such as salary information) should be restricted. Each "lack of access" requires a mark in a security file, so this situation matches the negative attendance method.

The mathematics behind which method is the best remains the same. If we’re designing a system for resale to many different clients, our choice of any one method will be a burden (in the form of extra data entry) to those whose security style best fits the other two methods. So, our challenge is to implement a server-side security layer that allows the client to choose which method it would like to use.

Oracle roles by themselves will not serve our purpose. This is because roles govern access to data objects, such as tables, rather than access to individual rows within the table.

Additional benefits to server-side security

If we enforce security access to the data in the client code, then we must teach the proper rules to each programmer who builds that code. And human frailty being what it is, we cannot safely assume that will suffice! So, we’ll also have to test each client-side program for security conformance and plan for a certain percentage of rework and retesting.

Sadly, all that work is not enough, because power users want access to SQL and more users are expecting to employ one of the many ad hoc query tools now on the market. That means we must do extra setup work with the ad hoc reporting software in order to secure the data access.

Background tables

We’ll use the tables shown in Listing A to practice implementing our security goals. The company_control table stores, on a company-by-company basis, the security method to use. The employee table serves as our example data. The user_company_access table allows (or restricts) access to a company, depending upon the security method employed.

Listing A: We’ll use these tables to implement server-side security on a per-user/per-company basis.

CREATE TABLE company_control
(company_id       NUMBER(3,0)  NOT NULL
,name             VARCHAR2(30) NOT NULL
,security_method  CHAR(1)
);

ALTER TABLE company_control
ADD (CONSTRAINT comc_pk PRIMARY KEY (company_id)
    ,CONSTRAINT comc_cc_security_method 
          CHECK (security_method in ('P','N','O'))
                /* (P)ositive, (N)egative, (O)pen access */
    );

CREATE TABLE employee
(company_id       NUMBER(3,0)  NOT NULL
,employee_id      VARCHAR2(9)  NOT NULL
,name             VARCHAR2(30) NOT NULL
);

ALTER TABLE employee
ADD (CONSTRAINT emp_pk PRIMARY KEY (company_id, employee_id)
    ,CONSTRAINT emp_fk_comc FOREIGN KEY (company_id)
                REFERENCES company_control (company_id)
    );

 CREATE TABLE user_company_access
(user_id          VARCHAR2(30) NOT NULL
,company_id       NUMBER(3,0)  NOT NULL
);

ALTER TABLE user_company_access
ADD (CONSTRAINT useca_pk PRIMARY KEY (user_id, company_id)
    ,CONSTRAINT useca_fk_comc FOREIGN KEY (company_id)
                REFERENCES company_control (company_id) 
    );

Our first positive step

Our plan of attack is to use views to implement our security goals in the server. If we knew which methods our users preferred, we could hard-code them into our views. (Of course, if they called for the open access method, we wouldn’t need any views!)

Let’s look at a view shown in Listing B that might implement the positive method. It selects each of the columns from the employee table, but shows employees only for companies that the user is authorized to see (denoted by an entry in the user_company_access table). Because this view assumes the use of the positive method, we don’t need to look in the company_control table to check the value of security_method. This view is fairly efficient at data retrieval, but because of a weakness

of the Oracle7 SQL parser, we can’t use it for inserting, updating, or deleting data. Even though the SELECT clause is limited to columns in only one table, the SQL parser sees two tables in the FROM clause and allows only a SELECT command. Later versions of Oracle should correct this, perhaps as early as version 7.3.

Listing B: This view implements security on a per-user/per-company basis - the positive attendance style - for SELECT commands only.

CREATE VIEW employee_view AS
SELECT empl.company_id
      ,empl.employee_id
      ,empl.name
 FROM  employee            empl
      ,user_company_access useca
 WHERE empl.company_id  = useca.company_id
   AND useca.user_id    = USER
 WITH CHECK OPTION;

To overcome the limitations in the server, we can use the view in Listing C. While this view allows us to modify the contents of the employee table, it’s much slower for data retrieval because of the correlated subquery. Incidentally, the WITH CHECK OPTION

directs Oracle to verify that any data entered through the view could also be queried by the view. This critical feature prevents users from using the "shot in the dark" approach to altering data they’re not authorized to see.

Listing C: This view implements security on a per-user/per-company basis—the positive attendance style—including INSERTs, UPDATEs, and DELETEs, but is slower on SELECTs.

CREATE VIEW employee_view AS
SELECT empl.company_id
      ,empl.employee_id
      ,empl.name
 FROM  employee            empl
 WHERE empl.company_id IN
   (SELECT useca.company_id
      FROM user_company_access useca
     WHERE useca.user_id    = USER
   )
 WITH CHECK OPTION;

A step in another direction

Conversely, we can implement the negative method with views, as shown in Listing D. Basically, we want to select all employee records, except for those employees who work for companies for which the user has been specifically denied access. Our design is flexible enough that the user_company_access table can handle both the positive and negative methods! Again, because we know we’ll use the negative method, we don’t need to look up the value in company_ control.security_ method.

Listing D: This view implements security on a per-user/per-company basis - the negative attendance style.

CREATE VIEW employee_view AS
SELECT empl.company_id
      ,empl.employee_id
      ,empl.name
 FROM  employee            empl
 WHERE NOT EXISTS 
      ( SELECT 'X'
          FROM user_company_access useca
         WHERE useca.company_id = empl.company_id
           AND useca.user_id    = USER
      )
 WITH CHECK OPTION;

A closer view

Now that we’ve coded our proof of concept views, it’s time to put them all together into a comprehensive view that we can use regardless of the setting of company_control.security_ method. The view in Listing E consists of three WHERE clause fragments, each of which returns data based upon one of the three security methods.

Listing E: This view implements server-side security using all three methods.

CREATE VIEW employee_view
SELECT empl.company_id
      ,empl.employee_id
      ,empl.name
 FROM  employee            empl
 WHERE EXISTS /* Handles entire Open access method */
       (SELECT 'x'
          FROM company_control compc
         WHERE compc.security_method  = 'O'
           AND compc.company_id       = empl.company_id
       )
    OR EXISTS /* Handles entire Positive method */
       (SELECT 'x'
          FROM user_company_access useca
              ,company_control     compc
         WHERE compc.security_method = 'P'
           AND compc.company_id      = empl.company_id
           AND useca.company_id      = compc.company_id
           AND useca.user_id         = USER
       )
    OR EXISTS /* Handles the entire Negative method */
       (SELECT 'x'
         FROM  company_control     compc
        WHERE compc.security_method = 'N'
          AND compc.company_id      = empl.company_id
          AND NOT EXISTS
              (SELECT 'x'
                FROM  user_company_access useca
                WHERE compc.company_id = useca.company_id
                  AND useca.user_id    = USER
              )
       )
 WITH CHECK OPTION;

On a per-company basis, each WHERE clause EXISTS fragment returns a mutually exclusive result. That means, for any given company, only one of the three fragments can possibly return a value. Thus, the view can’t report any employee of a company twice.

Let’s suppose that all of the companies use the same method. If we (and the SQL parser) are clever enough, the Oracle server should ignore two of the three EXISTS subqueries after checking the very small number of company_control records in the database.

Other directions

In the first two articles in this series, we discussed creating different views against the employee table for the Payroll and Job Cost applications. This concept is still valid - we just need to update the view in Listing E to reflect the special considerations in each application.

Is allowing each company to choose its own security method overkill? If so, then move the security_method column to its own table and join with that table rather than company_ control. Would you prefer to choose which security method to use on a per-user basis? Again, just shift the location of the security_ method column. Do you want to limit the access to individual accounts in the general ledger? Then create a user_account_access table, and make sure that the views also verify authorization against it.

Conclusion

Naturally, the views are not as efficient as working directly against the real table. Then again, if we have to implement security, we have to do it somewhere! If we filter out the data in the client software, then we’ll be sending data over the network that the client software will just throw away. That, too, is inefficient. It’s also less secure, because data sent over a network line can be intercepted en route.

Because the code in the view follows a pattern, we can automate the creation of a view for each table with a company_id. Next month, we’ll include two utilities to do just that - one for those who use Designer/2000 and one for those who don’t. In addition, we’ll explore a method that will enable us to generate the most code out of Designer/2000.

[The Cobb Group Home Page]

Copyright (c) 1996 The Cobb Group, a division of Ziff-Davis Publishing Company. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Ziff-Davis Publishing Company is prohibited. The Cobb Group and The Cobb Group logo are trademarks of Ziff-Davis Publishing Company.